package uk.ac.cam.echo.server.integrationtests;

import static org.junit.Assert.*;

import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import junit.framework.Assert;

import org.junit.Test;

import uk.ac.cam.echo.server.Database;

/**
 * @author Tom Smith
 */
public class TestFriendTable extends TestDatabase {

	@Test
	/**
	 * The two users should be friends.
	 */
	public void actualFriend() {
		try {
			// Execute SQL statement.
			Statement sqlStatement = super.m_Connection.createStatement();
			sqlStatement.executeUpdate("INSERT INTO " + Database.tblUser + " VALUES('1','Joe','Bloggs','Worker','34');");
			sqlStatement.executeUpdate("INSERT INTO " + Database.tblUser + " VALUES('3','Paul','Burns','Guest','7');");
			sqlStatement.executeUpdate("INSERT INTO " + Database.tblFriend + " VALUES('1','3');");
			sqlStatement.executeUpdate("INSERT INTO " + Database.tblFriend + " VALUES('3','1');");
			sqlStatement.close();

			// Commit changes to file, and release DB locks.
			super.m_Connection.commit();

			List<String> list = super.m_db.getFriendID("1");

			// Correct number of friends.
			Assert.assertEquals(1, list.size());

			// Correct friend.
			Assert.assertEquals("3", list.get(0));

		} catch (SQLException e) {
			fail("An SQL Exception occured - " + e.getMessage());
		}
	}

	@Test
	/**
	 * Add friend instance.
	 */
	public void addFriendInstanceTest() {
		try {
			// Execute SQL statement.
			Statement sqlStatement = super.m_Connection.createStatement();
			sqlStatement.executeUpdate("INSERT INTO " + Database.tblUser + " VALUES('1','Peter','Jones','Guest','112');");
			sqlStatement.executeUpdate("INSERT INTO " + Database.tblUser + " VALUES('2','Paul','Burns','Guest','527');");
			sqlStatement.close();

			// Commit changes to file, and release DB locks.
			super.m_Connection.commit();

			super.m_db.addFriendInstance("1", "2");
			
			Assert.assertFalse(super.m_db.areFriends("1", "2"));
			
			super.m_db.addFriendInstance("2", "1");
			
			Assert.assertTrue(super.m_db.areFriends("1", "2"));

		} catch (SQLException e) {
			fail("An SQL Exception occured - " + e.getMessage());
		}
	}
	
	@Test
	/**
	 * The user has already sent a friend request to this user.
	 */
	public void alreadyFriends() throws SQLException {
		try {
			// Execute SQL statement.
			Statement sqlStatement = super.m_Connection.createStatement();
			sqlStatement.executeUpdate("INSERT INTO " + Database.tblUser + " VALUES('1','Peter','Jones','Guest','112');");
			sqlStatement.executeUpdate("INSERT INTO " + Database.tblUser + " VALUES('2','Paul','Burns','Guest','527');");
			sqlStatement.close();

			// Commit changes to file, and release DB locks.
			super.m_Connection.commit();

			super.m_db.addFriendInstance("1", "2");
			super.m_db.addFriendInstance("1", "2");
			
		} catch (SQLException e) {
			fail("An SQL Exception occured - " + e.getMessage());
		}
	}


	@Test
	/**
	 * The two users should be friends.
	 */
	public void areFriends() {
		try {
			// Execute SQL statement.
			Statement sqlStatement = super.m_Connection.createStatement();
			sqlStatement.executeUpdate("INSERT INTO " + Database.tblUser + " VALUES('1','Joe','Bloggs','Worker','34');");
			sqlStatement.executeUpdate("INSERT INTO " + Database.tblUser + " VALUES('3','Paul','Burns','Guest','7');");
			sqlStatement.executeUpdate("INSERT INTO " + Database.tblFriend + " VALUES('1','3');");
			sqlStatement.executeUpdate("INSERT INTO " + Database.tblFriend + " VALUES('3','1');");
			sqlStatement.close();

			// Commit changes to file, and release DB locks.
			super.m_Connection.commit();

			Assert.assertTrue(super.m_db.areFriends("1", "3"));

		} catch (SQLException e) {
			fail("An SQL Exception occured - " + e.getMessage());
		}
	}

	@Test
	/**
	 * The two users should not be friends.
	 */
	public void areNotFriends() {
		try {
			// Execute SQL statement.
			Statement sqlStatement = super.m_Connection.createStatement();
			sqlStatement.executeUpdate("INSERT INTO " + Database.tblUser + " VALUES('1','Joe','Bloggs','Worker','34');");
			sqlStatement.executeUpdate("INSERT INTO " + Database.tblUser + " VALUES('3','Paul','Burns','Guest','7');");
			sqlStatement.executeUpdate("INSERT INTO " + Database.tblFriend + " VALUES('1','3');");
			sqlStatement.close();

			// Commit changes to file, and release DB locks.
			super.m_Connection.commit();

			Assert.assertFalse(super.m_db.areFriends("1", "3"));

		} catch (SQLException e) {
			fail("An SQL Exception occured - " + e.getMessage());
		}
	}

	@Test(expected=SQLException.class)
	/**
	 * The user must be in the User table to be in the Friend table.
	 */
	public void friendIsAUser() throws SQLException {
		// Execute SQL statement.
		Statement sqlStatement = super.m_Connection.createStatement();
		sqlStatement.executeUpdate("INSERT INTO " + Database.tblUser + 
				" VALUES('1','Bob','Jones','Guest','bob');");
		sqlStatement.executeUpdate("INSERT INTO " + Database.tblUser + 
				" VALUES('3','Chris','Hap','Guest','11');");

		sqlStatement.executeUpdate("INSERT INTO " + Database.tblFriend + " VALUES('1','2');");
		sqlStatement.close();

		// Commit changes to file, and release DB locks.
		super.m_Connection.commit();

		fail("User is friends with a non-existent user.");
	}

	@Test
	/**
	 * Only have the friend relation one way, so users are not friends.
	 */
	public void friendRequest() {
		try {
			// Execute SQL statement.
			Statement sqlStatement = super.m_Connection.createStatement();
			sqlStatement.executeUpdate("INSERT INTO " + Database.tblUser + " VALUES('14','Peter','Jones','Guest','112');");
			sqlStatement.executeUpdate("INSERT INTO " + Database.tblUser + " VALUES('5','Paul','Burns','Guest','527');");
			sqlStatement.executeUpdate("INSERT INTO " + Database.tblFriend + " VALUES('14','5');");
			sqlStatement.close();

			// Commit changes to file, and release DB locks.
			super.m_Connection.commit();

			List<String> list = super.m_db.getFriendRequests("5");

			// Correct number of friend requests - 1 because the relation is only in the table one way.
			Assert.assertEquals(1, list.size());
			
			// Correct RFID asking for friendship.
			Assert.assertEquals("14", list.get(0));

		} catch (SQLException e) {
			fail("An SQL Exception occured - " + e.getMessage());
		}
	}
	
	@Test(expected=SQLException.class)
	/**
	 * Check that a user cannot be friends with themselves.
	 */
	public void friendWithSelf() throws SQLException {
		// Execute SQL statement.
		Statement sqlStatement = super.m_Connection.createStatement();
		sqlStatement.executeUpdate("INSERT INTO " + Database.tblUser + 
				"(RFID,Forename,Surname,UserType) VALUES('1','Bob','Jones','Guest','bob');");

		sqlStatement.executeUpdate("INSERT INTO " + Database.tblFriend + " VALUES('1','1');");
		sqlStatement.close();

		// Commit changes to file, and release DB locks.
		super.m_Connection.commit();

		fail("User is friends with themselves.");
	}
	
	@Test
	/**
	 * Multiple friendships between different users.
	 */
	public void multipleFriends() {
		try {
			Statement sqlStatement = super.m_Connection.createStatement();

			// Insert users.
			sqlStatement.executeUpdate("INSERT INTO " + Database.tblUser + " VALUES('1','Joe','Bloggs','Guest','34');");
			sqlStatement.executeUpdate("INSERT INTO " + Database.tblUser + " VALUES('2','Paul','Burns','Guest','dfh');");
			sqlStatement.executeUpdate("INSERT INTO " + Database.tblUser + " VALUES('3','Harry','Scott','Guest','79');");
			sqlStatement.executeUpdate("INSERT INTO " + Database.tblUser + " VALUES('4','Adam','Bloggs','Guest','543');");
			sqlStatement.executeUpdate("INSERT INTO " + Database.tblUser + " VALUES('5','John','Red','Guest','431');");

			// Create friendships.
			sqlStatement.executeUpdate("INSERT INTO " + Database.tblFriend + " VALUES('1','2');");
			sqlStatement.executeUpdate("INSERT INTO " + Database.tblFriend + " VALUES('1','3');");
			sqlStatement.executeUpdate("INSERT INTO " + Database.tblFriend + " VALUES('1','4');");

			sqlStatement.executeUpdate("INSERT INTO " + Database.tblFriend + " VALUES('2','1');");
			sqlStatement.executeUpdate("INSERT INTO " + Database.tblFriend + " VALUES('2','4');");

			sqlStatement.executeUpdate("INSERT INTO " + Database.tblFriend + " VALUES('3','1');");
			sqlStatement.executeUpdate("INSERT INTO " + Database.tblFriend + " VALUES('3','2');");
			sqlStatement.executeUpdate("INSERT INTO " + Database.tblFriend + " VALUES('3','4');");

			sqlStatement.executeUpdate("INSERT INTO " + Database.tblFriend + " VALUES('4','1');");
			sqlStatement.executeUpdate("INSERT INTO " + Database.tblFriend + " VALUES('4','2');");


			sqlStatement.close();

			// Commit changes to file, and release DB locks.
			super.m_Connection.commit();

			List<String> list;

			// Correct number of friends - 1 = 3.
			list = super.m_db.getFriendID("1");
			Assert.assertEquals(3, list.size());

			// Correct number of friends - 2 = 2.
			list = super.m_db.getFriendID("2");
			Assert.assertEquals(2, list.size());

			// Correct number of friends - 3 = 1.
			list = super.m_db.getFriendID("3");
			Assert.assertEquals(1, list.size());

			// Correct number of friends - 4 = 2.
			list = super.m_db.getFriendID("4");
			Assert.assertEquals(2, list.size());

			// Correct number of friends - 5 = 0.
			list = super.m_db.getFriendID("5");
			Assert.assertEquals(0, list.size());

		} catch (SQLException e) {
			fail("An SQL Exception occured - " + e.getMessage());
		}
	}
	
	@Test
	/**
	 * Only have the friend relation one way, so users are not friends.
	 */
	public void onlyInFromUserID() {
		try {
			// Execute SQL statement.
			Statement sqlStatement = super.m_Connection.createStatement();
			sqlStatement.executeUpdate("INSERT INTO " + Database.tblUser + " VALUES('14','Peter','Jones','Guest','112');");
			sqlStatement.executeUpdate("INSERT INTO " + Database.tblUser + " VALUES('5','Paul','Burns','Guest','527');");
			sqlStatement.executeUpdate("INSERT INTO " + Database.tblFriend + " VALUES('5','14');");
			sqlStatement.close();

			// Commit changes to file, and release DB locks.
			super.m_Connection.commit();

			List<String> list = super.m_db.getFriendID("5");

			// Correct number of friends - 0 because the relation is only in the table one way.
			Assert.assertEquals(0, list.size());

		} catch (SQLException e) {
			fail("An SQL Exception occured - " + e.getMessage());
		}
	}
	
	@Test
	/**
	 * Only have the friend relation one way, so users are not friends.
	 */
	public void onlyInToUserID() {
		try {
			// Execute SQL statement.
			Statement sqlStatement = super.m_Connection.createStatement();
			sqlStatement.executeUpdate("INSERT INTO " + Database.tblUser + " VALUES('14','Peter','Jones','Guest','112');");
			sqlStatement.executeUpdate("INSERT INTO " + Database.tblUser + " VALUES('5','Paul','Burns','Guest','527');");
			sqlStatement.executeUpdate("INSERT INTO " + Database.tblFriend + " VALUES('14','5');");
			sqlStatement.close();

			// Commit changes to file, and release DB locks.
			super.m_Connection.commit();

			List<String> list = super.m_db.getFriendID("5");

			// Correct number of friends - 0 because the relation is only in the table one way.
			Assert.assertEquals(0, list.size());

		} catch (SQLException e) {
			fail("An SQL Exception occured - " + e.getMessage());
		}
	}
}
